/*
 *  Copyright 2008-2010 www.jrails.org
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */

package org.rails.core.sql;

import java.lang.reflect.Constructor;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.rails.core.db.table.AttributeMap;
import org.rails.core.db.table.Table;
import org.rails.core.json.SimpleJson;
import org.rails.core.model.ActiveRecord;
import org.rails.core.util.Utils;

/**
 * 构建SQL语句管理器,使用SQLManager可以动态构建复杂的查询语句或者其它语句。
 * @author jack
 *
 */
public final class SQLManager {

	protected final Logger logger = Logger.getLogger("SQLManager");

	private final Map<String, Object> parameter = new HashMap<String, Object>();
	private ActiveRecord record;

	private final List<String> columns = new ArrayList<String>();
	private final List<String> orders = new ArrayList<String>();
	private final List<String> groups = new ArrayList<String>();
	private final List<String> conditions = new ArrayList<String>();
	private final List<String> joins = new ArrayList<String>();
	private final List<String> havings = new ArrayList<String>();
	private final List<String> firsts = new ArrayList<String>();
	private final List<String> lasts = new ArrayList<String>();

	private int maxResult = 0;
	private boolean union = false;

	private Table table;
	
	/**
	 * 根据ActiveRecord构造新的SQLManager。
	 * @param record
	 */
	public SQLManager(final ActiveRecord record) {
		super();
		this.record = record;
		table = record.getTable();
		parameter.putAll(record);
	}
	
	/**
	 * 根据原有的sqlManager复制到新的SQLManager。
	 * @param sqlManager 被复制对象。
	 * @return
	 */
	public static SQLManager clone(final SQLManager sqlManager){
		final SQLManager sql = new SQLManager((ActiveRecord)sqlManager.getRecord().clone());
		sql.setColumns(sqlManager.getColumns());
		sql.setOrders(sqlManager.getOrders());
		sql.setGroups(sqlManager.getGroups());
		sql.setConditions(sqlManager.getConditions());
		sql.setJoins(sqlManager.getJoins());
		sql.setHavings(sqlManager.getHavings());
		sql.setFirsts(sqlManager.getFirsts());
		sql.setLasts(sqlManager.getLasts());
		sql.setParameter(sqlManager.getParameter());
		sql.setMaxResult(sqlManager.getMaxResult());
		return sql;		
	}

	public void setColumns(List<String> columns){
		this.columns.addAll(columns);
	}
	
	public void setOrders(List<String> orders){
		this.orders.addAll(orders);
	}

	public void setGroups(List<String> groups){
		this.groups.addAll(groups);
	}

	public void setConditions(List<String> conditions){
		this.conditions.addAll(conditions);
	}
	
	public void setJoins(List<String> joins){
		this.joins.addAll(joins);
	}
	
	public void setHavings(List<String> havings){
		this.havings.addAll(havings);
	}
	
	public void setFirsts(List<String> firsts){
		this.firsts.addAll(firsts);
	}
	
	public void setLasts(List<String> lasts){
		this.lasts.addAll(lasts);
	}
	
	public void addColumn(String column) {
		columns.add(column.trim());
	}

	public void addColumn(Table target) {
		columns.add(target.asNames());
	}
	
	public void addColumn(Table target,String...skipColumns) {
		columns.add(target.asNames(true,skipColumns));
	}

	public void insertColumn(int index, String column) {
		columns.add(index, column.trim());
	}

	public void removeColumn(int index) {
		columns.remove(index);
	}

	public void removeColumn(String column) {
		columns.remove(column.trim());
	}

	public void clearColumns() {
		columns.clear();
	}

	public boolean containColumn(String column) {
		return columns.contains(column);
	}

	public int getColumnsSize() {
		return columns.size();
	}

	public List<String> getColumns() {
		return columns;
	}

	public StringBuffer createColumns() {
		final StringBuffer column = new StringBuffer();
		columns.remove("");
		int size = columns.size();
		for (int i = 0; i < size; i++) {
			column.append(columns.get(i));
			if (i < size - 1)
				column.append(",");
		}
		if (size == 0)
			column.append("*");
		return column;
	}

	public void addOrder(String order) {
		orders.add(order.trim());
	}

	public void insertOrder(int index, String order) {
		orders.add(index, order.trim());
	}

	public void removeOrder(int index) {
		orders.remove(index);
	}

	public void removeOrder(String order) {
		orders.remove(order.trim());
	}

	public void clearOrders() {
		orders.clear();
	}

	public boolean containOrder(String order) {
		return orders.contains(order);
	}

	public int getOrdersSize() {
		return orders.size();
	}

	public List<String> getOrders() {
		return orders;
	}

	public StringBuffer createOrders() {
		final StringBuffer order = new StringBuffer();
		orders.remove("");
		int size = orders.size();
		for (int i = 0; i < size; i++) {
			order.append(orders.get(i));
			if (i < size - 1)
				order.append(",");
		}
		return order;
	}

	public void addGroup(String group) {
		groups.add(group.trim());
	}

	public void insertGroup(int index, String group) {
		groups.add(index, group.trim());
	}

	public void removeGroup(int index) {
		groups.remove(index);
	}

	public void removeGroup(String group) {
		groups.remove(group.trim());
	}

	public void clearGroups() {
		groups.clear();
	}

	public boolean containGroup(String group) {
		return groups.contains(group);
	}

	public int getGroupsSize() {
		return groups.size();
	}

	public List<String> getGroups() {
		return groups;
	}

	public StringBuffer createGroups() {
		final StringBuffer group = new StringBuffer();
		groups.remove("");
		int size = groups.size();
		for (int i = 0; i < size; i++) {
			group.append(groups.get(i));
			if (i < size - 1)
				group.append(",");
		}
		return group;
	}

	public void addCondition(String condition) {
		conditions.add(condition.trim());
	}

	public void insertCondition(int index, String condition) {
		conditions.add(index, condition.trim());
	}

	public void removeCondition(int index) {
		conditions.remove(index);
	}

	public void removeCondition(String condition) {
		conditions.remove(condition.trim());
	}

	public void clearConditions() {
		conditions.clear();
	}

	public boolean containCondition(String condition) {
		return conditions.contains(condition);
	}

	public int getConditionsSize() {
		return conditions.size();
	}

	public List<String> getConditions() {
		return conditions;
	}

	public StringBuffer createConditions() {
		final StringBuffer condition = new StringBuffer();
		conditions.remove("");
		int size = conditions.size();
		for (int i = 0; i < size; i++) {
			condition.append(conditions.get(i));
			if (i < size - 1)
				condition.append(" ");
		}
		return condition;
	}
	
	public StringBuffer createConds(){
		return createConditions();
	}

	public void addHaving(String having) {
		havings.add(having.trim());
	}

	public void insertHaving(int index, String having) {
		havings.add(index, having.trim());
	}

	public void removeHaving(int index) {
		havings.remove(index);
	}

	public void removeHaing(String having) {
		havings.remove(having.trim());
	}

	public void clearHavings() {
		havings.clear();
	}

	public boolean containHaing(String having) {
		return havings.contains(having);
	}

	public int getHavingsSize() {
		return havings.size();
	}

	public List<String> getHavings() {
		return havings;
	}

	public StringBuffer createHavings() {
		final StringBuffer having = new StringBuffer();
		havings.remove("");
		int size = havings.size();
		for (int i = 0; i < size; i++) {
			having.append(havings.get(i));
			if (i < size - 1)
				having.append(" ");
		}
		return having;
	}

	public void addFirst(String first) {
		firsts.add(first.trim());
	}

	public void insertFirst(int index, String first) {
		firsts.add(index, first.trim());
	}

	public void removeFirst(int index) {
		firsts.remove(index);
	}

	public void removeFirst(String first) {
		firsts.remove(first.trim());
	}

	public void clearFirsts() {
		firsts.clear();
	}

	public boolean containFirst(String first) {
		return firsts.contains(first);
	}

	public int getFirstsSize() {
		return firsts.size();
	}

	public List<String> getFirsts() {
		return firsts;
	}

	public StringBuffer createFirsts() {
		final StringBuffer first = new StringBuffer();
		firsts.remove("");
		int size = firsts.size();
		for (int i = 0; i < size; i++) {
			first.append(firsts.get(i));
			if (i < size - 1)
				first.append(" ");
		}
		return first;
	}

	public void addLast(String last) {
		lasts.add(last.trim());
	}

	public void insertLast(int index, String last) {
		lasts.add(index, last.trim());
	}

	public void removeLast(int index) {
		lasts.remove(index);
	}

	public void removeLast(String last) {
		lasts.remove(last.trim());
	}

	public void clearLasts() {
		lasts.clear();
	}

	public boolean containLast(String last) {
		return firsts.contains(last);
	}

	public int getLastsSize() {
		return firsts.size();
	}

	public List<String> getLasts() {
		return firsts;
	}

	public StringBuffer createLasts() {
		final StringBuffer last = new StringBuffer();
		lasts.remove("");
		int size = lasts.size();
		for (int i = 0; i < size; i++) {
			last.append(lasts.get(i));
			if (i < size - 1)
				last.append(" ");
		}
		return last;
	}

	public void addJoin(String join) {
		joins.add(join.trim());
	}

	public void insertJoin(int index, String join) {
		joins.add(index, join.trim());
	}

	public void removeJoin(int index) {
		joins.remove(index);
	}

	public void removeJoin(String join) {
		joins.remove(join.trim());
	}

	public void clearJoins() {
		joins.clear();
	}

	public boolean containJoin(String join) {
		return joins.contains(join);
	}

	public int getJoinsSize() {
		return joins.size();
	}

	public List<String> getJoins() {
		return joins;
	}

	public StringBuffer createJoins() {
		final StringBuffer join = new StringBuffer();
		joins.remove("");
		int size = joins.size();
		for (int i = 0; i < size; i++) {
			join.append(joins.get(i));
			if (i < size - 1)
				join.append(" ");
		}
		return join;
	}


	@SuppressWarnings("unchecked")
	public String createInsertSql() {
		final StringBuffer first = createFirsts();
		final StringBuffer columnList = new StringBuffer();
		final StringBuffer paramList = new StringBuffer();
		final StringBuffer last = createLasts();
		String columnName = null;
		AttributeMap am = null;
		try {
			Constructor<AttributeMap> con = (Constructor<AttributeMap>)table.getPKType().getConstructor(ActiveRecord.class);
			am = con.newInstance(record);
			final List<String> atts = am.getAttributes();
			for (int i = 0; i < atts.size(); i++) {
				columnName = atts.get(i).toLowerCase();
				columnList.append(table.formatColumn(columnName));
				paramList.append(am.getMapping(columnName));
				if (i < atts.size() - 1) {
					columnList.append(',');
					paramList.append(',');
				}
			}
			addParameter(record);
			return Utils.format("{0}INSERT INTO {1}({2}) VALUES({3}){4}",
					first,table.getFormatName(), columnList, paramList,last);
		} catch (Exception e) {
			logger.error("创建INSERT语句时发生错误   " + e.getMessage(),e);
			return null;
		}
	}

	@SuppressWarnings("unchecked")
	public String createUpdateSql() {
		final StringBuffer first = createFirsts();
		final StringBuffer condition = createConditions();
		final StringBuffer last = createLasts();
		if (!condition.toString().equals("")) {
			condition.insert(0, " WHERE ");
		}
		final StringBuffer columnList = new StringBuffer();
		String columnName;
		final List<String> atts = Utils.getKeys(record);
		atts.remove(table.getPrimaryKey());
		for (int i = 0; i < atts.size(); i++) {
			columnName = atts.get(i).toString();
			columnList.append(Utils.format("{0} = :{1}", table.formatColumn(columnName),columnName.toLowerCase()));
			if (i < atts.size() - 1)
				columnList.append(",");
		}
		return Utils.format("{0}UPDATE {1} SET {2}{3}{4}",first,table.getFormatName(),
				columnList, condition,last);
	}

	public String createDeleteSql() {
		final StringBuffer first = createFirsts();
		final StringBuffer condition = createConditions();
		final StringBuffer last = createLasts();
		if (!condition.toString().equals("")) {
			condition.insert(0, " WHERE ");
		}
		return Utils.format("{0}DELETE FROM {1}{2}{3}", first,table.getFormatName(), condition,last);
	}

	public String createQuerySql() {
		final StringBuffer first = createFirsts();
		final StringBuffer condition = createConditions();
		final StringBuffer join = createJoins();
		final StringBuffer column = createColumns();
		final StringBuffer group = createGroups();
		final StringBuffer having = createHavings();
		final StringBuffer order = createOrders();
		final StringBuffer last = createLasts();
		if (!first.toString().equals("")) {
			first.append(" ");
		}
		if (!condition.toString().equals("")) {
			condition.insert(0, " WHERE ");
		}
		if (!join.toString().equals("")) {
			join.insert(0, " ");
		}
		if (!group.toString().equals("")) {
			group.insert(0, " GROUP BY ");
		}
		if (!having.toString().equals("")) {
			having.insert(0, " HAVING ");
		}
		if (!order.toString().equals("")) {
			order.insert(0, " ORDER BY ");
		}
		if (!last.toString().equals("")) {
			last.insert(0, " ");
		}
		return Utils.format("{0}SELECT {1} FROM {2}{3}{4}{5}{6}{7}{8}", first,
				column, table.getFormatName(), join, condition, group, having, order,
				last);
	}
	
	public static String createQuerySql(String tableName,SimpleJson option){
	    String first = Utils.def(option.get("first"),"").toString().trim();
		String condition = Utils.def(option.get("condition"),"").toString().trim();
		String join = Utils.def(option.get("join"),"").toString().trim();
		String column = Utils.def(option.get("column"),"").toString().trim();
		String group = Utils.def(option.get("group"),"").toString().trim();
		String having = Utils.def(option.get("having"),"").toString().trim();
		String order = Utils.def(option.get("order"),"").toString().trim();
		String last = Utils.def(option.get("last"),"").toString().trim();
		
		if (!first.equals("")) {
			first = first + " ";
		}
		if (!condition.toString().equals("")) {
			condition = " WHERE " + condition;
		}
		if (!join.equals("")) {
			join = " " + join;
		}
		if (!group.equals("")) {
			group =  " GROUP BY " + group;
		}
		if (!having.equals("")) {
			having = " HAVING " + having;
		}
		if (!order.equals("")) {
			order = " ORDER BY " + order;
		}
		if (!last.equals("")) {
			last = " " + last;
		}
		return Utils.format("{0}SELECT {1} FROM {2}{3}{4}{5}{6}{7}{8}", first,
				Utils.def(column,"*"),tableName, join, condition, group, having, order,
				last);
	}
	
	public static SQLManager union(SQLManager...unions){
		final SQLManager sqlManager = SQLManager.clone(unions[0]);		
		for (int i = 1; i < unions.length; i++) {	
			sqlManager.union = true;
			sqlManager.addLast("UNION " + unions[i].createQuerySql());
			sqlManager.addParameter(unions[i].getParameter());
		}
		return sqlManager;
	}
	
	public static SQLManager unionAll(SQLManager...unions){
		final SQLManager sqlManager = SQLManager.clone(unions[0]);
		for (int i = 1; i < unions.length; i++) {	
			sqlManager.union = true;
			sqlManager.addLast("UNION ALL " + unions[i].createQuerySql());
			sqlManager.addParameter(unions[i].getParameter());
		}
		return sqlManager;
	}

	public void addParameter(String key, Object value) {
		parameter.put(key, value);
	}

	public void removeParameter(String key) {
		parameter.remove(key);
	}
	
	public void addParameter(Map<String, Object> parameter) {
		this.parameter.putAll(parameter);
	}

	public void setParameter(Map<String, Object> parameter) {
		this.parameter.clear();
		this.parameter.putAll(parameter);
	}

	public Map<String, Object> getParameter() {
		return parameter;
	}
	
	public void clearParameter() {
		 parameter.clear();
	}

	public void setMaxResult(int maxResult) {
		this.maxResult = maxResult;
	}

	public int getMaxResult() {
		return maxResult;
	}

	public Table getTable() {
		return table;
	}
	
	public ActiveRecord getRecord(){
		return record;
	}
	
	public boolean isUnion(){
		return union;
	}
	
	public void addQuery(Query query){
		addCondition(query.getConditions());
		addParameter(query.getParameter());
	}
	

}
